﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace SellerProject.testfolder
{
    public class DAL
    {
        public DataSet GetAllUsersDS()
        {
            SqlConnection connection = new SqlConnection(CONNECTION_STRING);
            SqlCommand command = new SqlCommand("GetUsers", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(command);
            try
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            finally
            {
                connection.Dispose();
                command.Dispose();
                da.Dispose();
            }
        }

        public User GetUser(int userId)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand("GetUserById", connection);
            command.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
            SqlDataReader dr = null;
            try
            {
                connection.Open();
                dr = command.ExecuteReader(CommandBehavior.SingleRow);
                if (dr.Read())
                {
                    return PopulateUser(dr);
                }
                return null;
            }
            finally
            {
                if (dr != null && !dr.IsClosed)
                {
                    dr.Close();
                }
                connection.Dispose();
                command.Dispose();
            }
        }

        public User PopulateUser(IDataRecord dr)
        {
            User user = new User();
            user.UserId = Convert.ToInt32(dr["UserId"]);
            //检查 NULL 的示例
            if (dr["UserName"] != DBNull.Value)
            {
                user.UserName = Convert.ToString(dr["UserName"]);
            }
            user.Password = Convert.ToString(dr["Password"]);
            return user;
        }

        public UserCollection GetAllUsers()
        {
            SqlConnection connection = new SqlConnection(CONNECTION_STRING);
            SqlCommand command = new SqlCommand("GetAllUsers", connection);
            SqlDataReader dr = null;
            try
            {
                connection.Open();
                dr = command.ExecuteReader(CommandBehavior.SingleResult);
                UserCollection users = new UserCollection();
                while (dr.Read())
                {
                    users.Add(PopulateUser(dr));
                }
                return users;
            }
            finally
            {
                if (dr != null && !dr.IsClosed)
                {
                    dr.Close();
                }
                connection.Dispose();
                command.Dispose();
            }
        }

    }
}